<html lang="zh-CN">
<head>
  <meta charset="utf-8">
  <title>文档</title>
  <link href="../css/semantic/semantic.min.css" rel="stylesheet">
  <link href="../css/markdown.css" rel="stylesheet">
  <link href="../css/wtxynavigation.css" rel="stylesheet">
    <script src="../js/jquery.min.js"></script>
    <script type="text/javascript" src="../js/include.js"></script>
  <style>
    body{padding-top:0;background:#f7f7f7}
    body>.ui.container{width:100%;padding:0}
    #project-title{background:#6e6e6e;width:100%;line-height:3.572rem;color:#fff;font-size:1.2rem}
    #project-title .title-wrap{font-weight:700;padding:0 12px;width:100%;max-width:1366px!important;min-width:900px;margin:0 auto}
    #project-title .left,#project-title right{display:inline-block}
    #project-title .right{float:right}
    #project-title .right .wiki-actions .menu{left:auto;right:0;margin-top:.4em!important}
    #wiki-preview-container{width:100%;max-width:1366px!important;min-width:900px;padding:0;border:1px solid #e3e9ed;border-top:0}
    #wiki-preview>.ui.segment{margin-top:0;padding:1rem;padding-left:0;border:0}
    .hover-places{display:inline-block}
    #page-detail{width:60%;margin:0 auto}
    #page-detail .title{font-weight:700;font-size:24px;padding:12px 0;border-bottom:1px solid #ccc}
    #page-detail .content{margin-top:15px}
    .content p{word-wrap:break-word}
    #wiki-parent,#wiki-title,#wikis{display:none}
    .ui.grid{padding:0!important}
    #ex-all{cursor:pointer;color:#537c8d}
  </style>
</head>
<body>
  <div class="ui container">

      <div id="wtxyNav"></div>

    <div class="ui container" id="wiki-preview-container">
      <div id="wiki-preview">
        <div class="ui segment">
          <div id="page-detail" class="markdown-body">
            <div class='title'>6.FMDB基本使用</div><div class='content'><h1>
<a id="6fmdb基本使用" class="anchor" href="#6fmdb%E5%9F%BA%E6%9C%AC%E4%BD%BF%E7%94%A8"></a>6.FMDB基本使用</h1>
<h2>
<a id="描述" class="anchor" href="#%E6%8F%8F%E8%BF%B0"></a>描述</h2>
<ol>
<li>
<p>原生的<code>SQLite</code>数据库,是C语言的API, 使用起来比较复杂麻烦.</p>
</li>
<li>
<p>然后就出现了对其进行封装的库--<strong>FMDB</strong></p>
</li>
<li>
<p><strong>FMDB</strong>是对<code>libsqlite3</code>进行封装</p>
</li>
</ol>
<h2>
<a id="主要类型" class="anchor" href="#%E4%B8%BB%E8%A6%81%E7%B1%BB%E5%9E%8B"></a>主要类型</h2>
<ul>
<li>
<strong>FMDatabase</strong>:  一个<code>FMDatabase</code>对象代表一个单独的<code>SQLite</code>数据库，通过<code>SQLite</code>语句执行数据库的增删改查操作</li>
<li>
<strong>FMResultSet</strong>: 使用<code>FMDatabase</code>对象查询数据库后的结果集</li>
<li>
<strong>FMDatabaseQueue</strong>: 用于多线程操作数据库，它保证线程安全</li>
</ul>
<h2>
<a id="方法大观" class="anchor" href="#%E6%96%B9%E6%B3%95%E5%A4%A7%E8%A7%82"></a>方法大观:</h2>
<h3>
<a id="fmdatabase" class="anchor" href="#fmdatabase"></a>FMDatabase</h3>
<ul>
<li>
<code>-open</code>: 打开数据库</li>
<li>
<code>-tableExists</code> : 判断表是否存在</li>
<li>
<code>-executeStatements</code>: 创建表SQL语句执行方法</li>
<li>
<code>-executeQuery</code>:  查询(<code>SELECT * FROM .......</code>)使用的SQL语句执行方法</li>
<li>
<code>-executeUpdate</code>: <strong>增删改</strong>用FMDatabase的<code>-executeUpdate</code>
</li>
<li>
<code>-close</code>: 关闭数据库</li>
</ul>
<h3>
<a id="fmdatabasequeue" class="anchor" href="#fmdatabasequeue"></a>FMDatabaseQueue</h3>
<ul>
<li>
<code>+databaseQueueWithPath</code>: 给它一个数据库地址, 获取一个<code>FMDatabaseQueue</code>
</li>
<li>
<code>-inDatabase</code>:  获取一个线程安全的<code>FMDatabase</code>对象</li>
</ul>
<h3>
<a id="fmresultset" class="anchor" href="#fmresultset"></a>FMResultSet</h3>
<ul>
<li>
<code>-next</code>: 返回一个<code>BOOL</code>值,代表是否还有数据.</li>
<li>
<code>-stringForColumn</code>: 获取一个字符串类型的字段值</li>
<li>
<code>-longLongIntForColumn</code>: 获取一个longlong类型的字段值</li>
<li>
<code>-close</code>: <code>-next</code>返回<code>NO</code>代表没有, 查询完成以后关闭查询</li>
</ul>
<h3>
<a id="tip" class="anchor" href="#tip"></a>Tip:</h3>
<ul>
<li>除了查询(<code>SELECT * FROM .......</code>)用FMDatabase的<code>-executeQuery</code>, 其他操作(<strong>增删改</strong>)用FMDatabase的<code>-executeUpdate</code>
</li>
</ul>
<h2>
<a id="代码使用" class="anchor" href="#%E4%BB%A3%E7%A0%81%E4%BD%BF%E7%94%A8"></a>代码使用:</h2>
<h3>
<a id="创建fmdatabasequeue" class="anchor" href="#%E5%88%9B%E5%BB%BAfmdatabasequeue"></a>创建FMDatabaseQueue</h3>
<pre><code>
/**
 单例
 */
+ (nonnull instancetype)shared
{
    static SY_MiMsgDAO *instance;
    static dispatch_once_t onceToken;
    dispatch_once(&amp;onceToken, ^{
        
        
        instance = [[SY_MiMsgDAO alloc] init];
        
        NSString *dbPath = [[WTART_LocalFile pathForDocument] stringByAppendingPathComponent:@"xiaomiIMMsg.sqlite"];
        
        instance.fmdbQueue = [FMDatabaseQueue databaseQueueWithPath:dbPath];
        
        [instance createTables]; //创建数据库表
        
    });
    
    return instance;
}


</code></pre>
<h3>
<a id="创建表" class="anchor" href="#%E5%88%9B%E5%BB%BA%E8%A1%A8"></a>创建表</h3>
<pre><code>
- (void)createTablesUserInfo {
    
    if (self.fmdbQueue) {
        
        [self.fmdbQueue inDatabase:^(FMDatabase *  db) {
            
            if([db open]) {
                
                if (![db tableExists:DB_TABLE_NAME_UserInfoDATA]) {
                    
                    NSString *createTableSQL = [NSString stringWithFormat:@"CREATE TABLE %@ (id INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT, phone TEXT, avatar TEXT)", DB_TABLE_NAME_UserInfoDATA];
                    
                    if ([db executeStatements:createTableSQL]) {
                        
                        VSLogInfo(@"[SY_MiMsgDAO] Create Table Success!");
                        
                    } else {
                        
                        VSLogInfo(@"[SY_MiMsgDAO] Create Table Failed!");
                        
                    }
                    
                }
                
                
            }
            
            [db close];
            
        }];
        
    }
}

</code></pre>
<h3>
<a id="跟新表" class="anchor" href="#%E8%B7%9F%E6%96%B0%E8%A1%A8"></a>跟新表</h3>
<pre><code>
/**
 清空未读
 */
- (void)resetXiaoMiMsgUnReadStateFromChatUser:(NSString *)chatUser
                               completeHandle:(SYMiMsgDAOUnReadStateResetBlock)completeHandle;
{
    if (!chatUser &amp;&amp; chatUser.length == 0) {

        if(completeHandle) completeHandle(NO);
        return;
    }
    
    [self.fmdbQueue inDatabase:^(FMDatabase *  db) {
        
        
        if ([db open]) {
            
            NSString *query = [NSString stringWithFormat:@"UPDATE %@ SET readState = 0 where chatUser = %@", DB_TABLE_NAME_XiMiMsgDATA, chatUser];
            
            if ([db executeUpdate:query]) {
                
                VSLogInfo(@"[SY_MiMsgDAO] UPDATEUPDATE  data success!");
                if(completeHandle) completeHandle(YES);
            }else{
                
                VSLogInfo(@"[SY_MiMsgDAO] UPDATEUPDATE  data failed!");
                if(completeHandle) completeHandle(NO);
            }
            
            [db close];
        }
        
        
    }];
}

</code></pre>
<h3>
<a id="表查询" class="anchor" href="#%E8%A1%A8%E6%9F%A5%E8%AF%A2"></a>表查询</h3>
<pre><code>
/**
 查询客服们的未读数
 */
- (void)queryXiaoMiMsgUnReadStateServerMsgs:(NSArray&lt;SYTSDKChatGetCustomerListItem *&gt; *)msgs
                             completeHandle:(SYMiMsgDAOQueryUnReadStateServerBlock)completeHandle
{
    if (msgs.count == 0) {
        if (completeHandle) completeHandle(msgs);
        return;
    }
    
    [self.fmdbQueue inDatabase:^(FMDatabase * db) {
                        
        if ([db open]) {
                        
            for (SYTSDKChatGetCustomerListItem * item in msgs) {
                
                NSInteger unReadCount = 0;
                if (!item.chatUser) {
                    
                    VSLogDebug(@"[没有 chatUser 查毛线], 💣💣💣💣💣💣💣💣");
                    continue;
                }
                
                NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE chatUser = %@ AND readState = 1", DB_TABLE_NAME_XiMiMsgDATA, item.chatUser];
                
                
                
                FMResultSet *rs = [db executeQuery:querySQL];
                
                while ([rs next]) {
                    
                    unReadCount++;
                }
                
                item.unReadCount = [NSString stringWithFormat:@"%zd", unReadCount];
            }
        }
        
        if (completeHandle) completeHandle(msgs);
    }];
    
}

</code></pre>
<h2>
<a id="扩展链接" class="anchor" href="#%E6%89%A9%E5%B1%95%E9%93%BE%E6%8E%A5"></a>扩展链接</h2>
<p><a href="https://www.jianshu.com/p/7958d31c2a97">简书-FMDB 使用方法</a></p></div>
          </div>
        </div>
      </div>
      </div>
  </div>
</body>
</html>